{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Comparison with pandas\n",
    "\n",
    "[pandas](https://pandas.pydata.org) is a popular Python library for data analysis and manipulation. \n",
    "\n",
    "GreenplumPython strives to provide a pandas-like interface so that people can get started with it quickly."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<module 'greenplumpython' from '/home/gpadmin/.local/lib/python3.9/site-packages/greenplumpython/__init__.py'>"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import greenplumpython as gp\n",
    "\n",
    "gp"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "However, the two packages serve very different purposes, i.e.,\n",
    "\n",
    "- GreenplumPython is an interface to a remote database system, while\n",
    "- pandas is a library for manipulating local in-memory data.\n",
    "\n",
    "There are still some important differences in their interface.\n",
    "\n",
    "This document covers the similarities and the differences between GreenplumPython and pandas, as well as the rationales behind.\n",
    "\n",
    "## Data Structure\n",
    "\n",
    "GreenplumPython's core data structure `DataFrame` is fundamentally similar to `Dataframe` in pandas in that\n",
    "\n",
    "- Data are organized into rows and columns;\n",
    "- Columns can be of different types, and can be accessed by name;\n",
    "- Rows are of the same type, and are iterable.\n",
    "\n",
    "Next, we will see similarities and differences between them in detail with examples.\n",
    "\n",
    "### Getting Access to the Structure\n",
    "\n",
    "For example, suppose we have some information of students, including students' names and ages:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('alice', 18), ('bob', 19), ('carol', 19)]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "students = [(\"alice\", 18), (\"bob\", 19), (\"carol\", 19)]\n",
    "students"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For analyzing them, we might want to create a pandas `DataFrame` as follows:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>alice</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bob</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>carol</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name  age\n",
       "0  alice   18\n",
       "1    bob   19\n",
       "2  carol   19"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd_df = pd.DataFrame.from_records(students, columns=[\"name\", \"age\"])\n",
    "pd_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also create a `DataFrame` in GreenplumPython from the same data in a very similar way:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "\t<tr>\n",
       "\t\t<th>name</th>\n",
       "\t\t<th>age</th>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>alice</td>\n",
       "\t\t<td>18</td>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>bob</td>\n",
       "\t\t<td>19</td>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>carol</td>\n",
       "\t\t<td>19</td>\n",
       "\t</tr>\n",
       "</table>"
      ],
      "text/plain": [
       "-------------\n",
       " name  | age \n",
       "-------+-----\n",
       " alice |  18 \n",
       " bob   |  19 \n",
       " carol |  19 \n",
       "-------------\n",
       "(3 rows)"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "db = gp.database(\"postgresql://localhost/gpadmin\")\n",
    "gp_df = gp.DataFrame.from_rows(students, column_names=[\"name\", \"age\"], db=db)\n",
    "gp_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But here is an important **difference**:\n",
    "\n",
    "- a `DataFrame` in GreenplumPython must be created in a database, while\n",
    "- pandas does not have the concept of \"database\".\n",
    "\n",
    "`Database` in GreenplumPython is like \"directory\" in file systems, which helps to avoid name conflict on persistence.\n",
    "\n",
    "A GreenplumPython `DataFrame` can be saved persistently in the database system as a table with"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "\t<tr>\n",
       "\t\t<th>name</th>\n",
       "\t\t<th>age</th>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>alice</td>\n",
       "\t\t<td>18</td>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>bob</td>\n",
       "\t\t<td>19</td>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>carol</td>\n",
       "\t\t<td>19</td>\n",
       "\t</tr>\n",
       "</table>"
      ],
      "text/plain": [
       "-------------\n",
       " name  | age \n",
       "-------+-----\n",
       " alice |  18 \n",
       " bob   |  19 \n",
       " carol |  19 \n",
       "-------------\n",
       "(3 rows)"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gp_df.save_as(\"student\", column_names=[\"name\", \"age\"], temp=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is similar to how a pandas `DataFrame` is persisted as file:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "pd_df.to_csv(\"/tmp/student.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Saving a `DataFrame` with `temp=True` in GreenplumPython is similar to saving a `DataFrame` into the `/tmp` directory in pandas:\n",
    "\n",
    "- GreenplumPython's DataFrames saved with `temp=True` will be dropped automatically by the database system when the database session is terminated, while\n",
    "- pandas' DataFrames saved in `/tmp` will be clean automatically by the operating system."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In order to access the data of a table in database, we can create a `DataFrame` from the table:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "\t<tr>\n",
       "\t\t<th>name</th>\n",
       "\t\t<th>age</th>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>alice</td>\n",
       "\t\t<td>18</td>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>bob</td>\n",
       "\t\t<td>19</td>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>carol</td>\n",
       "\t\t<td>19</td>\n",
       "\t</tr>\n",
       "</table>"
      ],
      "text/plain": [
       "-------------\n",
       " name  | age \n",
       "-------+-----\n",
       " alice |  18 \n",
       " bob   |  19 \n",
       " carol |  19 \n",
       "-------------\n",
       "(3 rows)"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "student = db.create_dataframe(table_name=\"student\")\n",
    "student"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is similar to loading a `DataFrame` from file in pandas, that is,"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>alice</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>bob</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>carol</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Unnamed: 0   name  age\n",
       "0           0  alice   18\n",
       "1           1    bob   19\n",
       "2           2  carol   19"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv(\"/tmp/student.csv\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Accessing Data in Rows and Columns\n",
    "\n",
    "In both GreenplumPython `DataFrame`s and pandas `DataFrame`s, rows can be accessed by iterating over the dataframe.\n",
    "\n",
    "For example, in GreenplumPython:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "alice 18\n",
      "bob 19\n",
      "carol 19\n"
     ]
    }
   ],
   "source": [
    "for row in gp_df:\n",
    "    print(row[\"name\"], row[\"age\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This is similar to how rows in a pandas `DataFrame` can be accessed:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "alice 18\n",
      "bob 19\n",
      "carol 19\n"
     ]
    }
   ],
   "source": [
    "for row in pd_df.iterrows():\n",
    "    print(row[1][\"name\"], row[1][\"age\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Similar to pandas, `Row` in GreenplumPython is `dict`-like. The value of each column can be accessed by name.\n",
    "\n",
    "In conclusion, from the user's perspective, `DataFrame` in GreenplumPython is very similar to `DataFrame` in pandas. We expect this would make it easier for whoever interested to get started using GreenplumPython."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Selection\n",
    "\n",
    "Data selection is probably the most fundamental set of operations on data.\n",
    "\n",
    "In both pandas and GreenplumPython, data selection is done primarily with the `[]` operator."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Selecting Columns\n",
    "\n",
    "In both pandas and GreenplumPython, columns are accessed by name.\n",
    "\n",
    "For example, to select a subset of columns, such as `name` and `age`, from the dataframe containing student info, in pandas we can do:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>alice</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bob</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>carol</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name  age\n",
       "0  alice   18\n",
       "1    bob   19\n",
       "2  carol   19"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd_df[[\"name\", \"age\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The result of the `[]` operator is a new pandas `DataFrame`. In GreenplumPython, this is exactly the same:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "\t<tr>\n",
       "\t\t<th>name</th>\n",
       "\t\t<th>age</th>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>alice</td>\n",
       "\t\t<td>18</td>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>bob</td>\n",
       "\t\t<td>19</td>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>carol</td>\n",
       "\t\t<td>19</td>\n",
       "\t</tr>\n",
       "</table>"
      ],
      "text/plain": [
       "-------------\n",
       " name  | age \n",
       "-------+-----\n",
       " alice |  18 \n",
       " bob   |  19 \n",
       " carol |  19 \n",
       "-------------\n",
       "(3 rows)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "student[[\"name\", \"age\"]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The result is a new GreenplumPython `DataFrame` containing the selected columns."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Accessing a Single Column\n",
    "\n",
    "To refer to a single column, we can use the `[]` operator with the column name in both pandas and GreenplumPython.\n",
    "\n",
    "For example, to access the names of the students in pandas, we can do:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0    alice\n",
       "1      bob\n",
       "2    carol\n",
       "Name: name, dtype: object"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd_df[\"name\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "While in GreenplumPython, the column can be refered to in the same way:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "<greenplumpython.col.Column at 0x7fa4e536f880>"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "gp_df[\"name\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But you might notice the **difference** here:\n",
    "\n",
    "- for pandas, using the `[]` operator gives us the data immediately if we refer to a column, while\n",
    "- for GreenplumPython, it only gives a **symbolic** `Column` object. `Column` is supposed to be used for computation rather than for observing data.\n",
    "\n",
    "The reasons behind this difference are:\n",
    "\n",
    "- Database systems behind GreenplumPython does not provide native one-dimensional data structure like `Series` in pandas.\n",
    "- It is much more efficient to retrieve all columns needed in a GreenplumPython `DataFrame` at once than one at a time. \n",
    "\n",
    "We will see later how to add new columns to a GreenplumPython `DataFrame` so that they can be retrieved all at once."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Selecting Rows by Predicates\n",
    "\n",
    "The `[]` operator can also be used to select a subset of rows, a.k.a filtering.\n",
    "\n",
    "Say we want the information of student named \"alice\", with pandas we can do:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>alice</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name  age\n",
       "0  alice   18"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd_df[lambda df: df[\"name\"] == \"alice\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With GreenplumPython, we can do it in exactly the same way:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "\t<tr>\n",
       "\t\t<th>name</th>\n",
       "\t\t<th>age</th>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>alice</td>\n",
       "\t\t<td>18</td>\n",
       "\t</tr>\n",
       "</table>"
      ],
      "text/plain": [
       "-------------\n",
       " name  | age \n",
       "-------+-----\n",
       " alice |  18 \n",
       "-------------\n",
       "(1 row)"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "student[lambda t: t[\"name\"] == \"alice\"]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Here we see how a column in GreenplumPython, `t[\"name\"]` in this case, is used for computation to form a more complex expression.\n",
    "\n",
    "In this example, When the expression `t[\"name\"] == \"alice\"` is evaluated, `t` will be bound to the **current** dataframe, i.e. `student`.\n",
    "\n",
    "GreenplumPython provides such a functional interface so that the user does not have to refer to the possibly long intermediate variable name like `student` again and again when the expression becomes complicated."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Selecting Rows by Slices\n",
    "\n",
    "We can get a quick glance of the data by selecting the first several rows. This can be achieved with `slice` in Python.\n",
    "\n",
    "Like many built-in data structures in Python, such as `list` and `tuple`, `DataFrame` in GreenplumPython supports slicing.\n",
    "\n",
    "For example, if we want only the first two rows of the `DataFrame` of students in GreenplumPython, we can do:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "\t<tr>\n",
       "\t\t<th>name</th>\n",
       "\t\t<th>age</th>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>alice</td>\n",
       "\t\t<td>18</td>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>bob</td>\n",
       "\t\t<td>19</td>\n",
       "\t</tr>\n",
       "</table>"
      ],
      "text/plain": [
       "-------------\n",
       " name  | age \n",
       "-------+-----\n",
       " alice |  18 \n",
       " bob   |  19 \n",
       "-------------\n",
       "(2 rows)"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "student[:2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In pandas, we can do exactly the same thing on a `DataFrame`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>alice</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bob</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name  age\n",
       "0  alice   18\n",
       "1    bob   19"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd_df[:2]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But you might notice the **difference**: When selecting rows,\n",
    "\n",
    "- for pandas, rows in the output `DataFrame` preserves the same order as the input, while\n",
    "- for GreenplumPython, the order of rows in `DataFrame` might not be preserved.\n",
    "\n",
    "The difference is due to the fact that database systems behind will not guarantee the order of rows unless otherwise specified.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Transformation\n",
    "\n",
    "Data transformation is about changing the data to a desired form.\n",
    "\n",
    "Like pandas, GreenplumPython provides powerful building blocks to make transformation easier.\n",
    "\n",
    "### Data Ordering\n",
    "\n",
    "Having the data sorted in a desired order makes it convenient for many analytical tasks, such as statistics.\n",
    "\n",
    "pandas supports sorting the data (a.k.a values) of a `DataFrame` by columns.\n",
    "\n",
    "For example, we can sort in pandas the dataframe of student info by \"age\" and then \"name\", both in descending order with:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>carol</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bob</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>alice</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name  age\n",
       "2  carol   19\n",
       "1    bob   19\n",
       "0  alice   18"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd_df.sort_values([\"age\", \"name\"], ascending=[False, False])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In GreenplumPython, order of data can be defined with the `order_by()` method:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "\t<tr>\n",
       "\t\t<th>name</th>\n",
       "\t\t<th>age</th>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>carol</td>\n",
       "\t\t<td>19</td>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>bob</td>\n",
       "\t\t<td>19</td>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>alice</td>\n",
       "\t\t<td>18</td>\n",
       "\t</tr>\n",
       "</table>"
      ],
      "text/plain": [
       "-------------\n",
       " name  | age \n",
       "-------+-----\n",
       " carol |  19 \n",
       " bob   |  19 \n",
       " alice |  18 \n",
       "-------------\n",
       "(3 rows)"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "student.order_by(\"age\", ascending=False).order_by(\"name\", ascending=False)[:]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "There are some important **difference** compared with pandas:\n",
    "\n",
    "- GreenplumPython does not provide something like `DataFrame.sort_index()` in pandas, because `DataFrame` in GreenplumPython does not have an \"index column\".\n",
    "- In GreenplumPython, slicing is requied after `order_by()` to get an ordered `DataFrame` due to the limitations of relational database systems."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Column Transformation\n",
    "\n",
    "Column transformation is to transform one or more existing columns into a new one of the same length.\n",
    "\n",
    "A new column may contains data resulting from whatever computation we want. \n",
    "\n",
    "Both GreeplumPython and pandas support transforming columns by adding a new column. Specifically, we need to:\n",
    "\n",
    "- define the transfomation as an expression, and then\n",
    "- bind the expression to a new column of the source `DataFrame` or `DataFrame` to form a new one.\n",
    "\n",
    "We can use `assign()` method to add new columns in both packages. For example, suppose we would like to know the year of birth for each student in the previous example.\n",
    "\n",
    "In pandas, we can add a new column named `year_of_birth` like this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "      <th>year_of_birth</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>alice</td>\n",
       "      <td>18</td>\n",
       "      <td>2005</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bob</td>\n",
       "      <td>19</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>carol</td>\n",
       "      <td>19</td>\n",
       "      <td>2004</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name  age  year_of_birth\n",
       "0  alice   18           2005\n",
       "1    bob   19           2004\n",
       "2  carol   19           2004"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import datetime\n",
    "\n",
    "this_year = datetime.date.today().year\n",
    "pd_df.assign(year_of_birth=lambda df: -df[\"age\"] + this_year)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In GreenplymPython, we can do exactly the same:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "\t<tr>\n",
       "\t\t<th>name</th>\n",
       "\t\t<th>age</th>\n",
       "\t\t<th>year_of_birth</th>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>bob</td>\n",
       "\t\t<td>19</td>\n",
       "\t\t<td>2004</td>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>carol</td>\n",
       "\t\t<td>19</td>\n",
       "\t\t<td>2004</td>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>alice</td>\n",
       "\t\t<td>18</td>\n",
       "\t\t<td>2005</td>\n",
       "\t</tr>\n",
       "</table>"
      ],
      "text/plain": [
       "-----------------------------\n",
       " name  | age | year_of_birth \n",
       "-------+-----+---------------\n",
       " bob   |  19 |          2004 \n",
       " carol |  19 |          2004 \n",
       " alice |  18 |          2005 \n",
       "-----------------------------\n",
       "(3 rows)"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "student.assign(year_of_birth=lambda t: -t[\"age\"] + this_year)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The column data can result from any expression, which can contain complex computations.\n",
    "\n",
    "For example, in order to hide the names of students to protect privacy, we can write a function transforming names to something not human-readable:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {},
   "outputs": [],
   "source": [
    "from hashlib import sha256\n",
    "\n",
    "\n",
    "@gp.create_function\n",
    "def hash_name(name: str) -> str:\n",
    "    return sha256(name.encode(\"utf-8\")).hexdigest()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `gp.create_function` decorator converts a Python function into a User-Defined Function (UDF) in database so that it can be applied to `Column`s.\n",
    "\n",
    "With the function defined, we can then apply it to generate a new `Column`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "\t<tr>\n",
       "\t\t<th>name</th>\n",
       "\t\t<th>age</th>\n",
       "\t\t<th>name_</th>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>bob</td>\n",
       "\t\t<td>19</td>\n",
       "\t\t<td>81b637d8fcd2c6da6359e6963113a1170de795e4b725b84d1e0b4cfd9ec58ce9</td>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>carol</td>\n",
       "\t\t<td>19</td>\n",
       "\t\t<td>4c26d9074c27d89ede59270c0ac14b71e071b15239519f75474b2f3ba63481f5</td>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>alice</td>\n",
       "\t\t<td>18</td>\n",
       "\t\t<td>2bd806c97f0e00af1a1fc3328fa763a9269723c8db8fac4f93af71db186d6e90</td>\n",
       "\t</tr>\n",
       "</table>"
      ],
      "text/plain": [
       "--------------------------------------------------------------------------------\n",
       " name  | age | name_                                                            \n",
       "-------+-----+------------------------------------------------------------------\n",
       " bob   |  19 | 81b637d8fcd2c6da6359e6963113a1170de795e4b725b84d1e0b4cfd9ec58ce9 \n",
       " carol |  19 | 4c26d9074c27d89ede59270c0ac14b71e071b15239519f75474b2f3ba63481f5 \n",
       " alice |  18 | 2bd806c97f0e00af1a1fc3328fa763a9269723c8db8fac4f93af71db186d6e90 \n",
       "--------------------------------------------------------------------------------\n",
       "(3 rows)"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "student.assign(name_=lambda t: hash_name(t[\"name\"]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "After adding the new column, we can select the columns we care about into a new `DataFrame` with the `[]` operator.\n",
    "\n",
    "To be more concise, GreenplumPython and pandas support transforming columns directly into a new `DataFrame` or `DataFrame` by `apply()`-ing the function.\n",
    "\n",
    "In the previous example, using `apply()`, we can obtain the GreenplumPython `DataFrame` with the original names hidden:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 25,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "\t<tr>\n",
       "\t\t<th>name</th>\n",
       "\t\t<th>age</th>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>2bd806c97f0e00af1a1fc3328fa763a9269723c8db8fac4f93af71db186d6e90</td>\n",
       "\t\t<td>18</td>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>81b637d8fcd2c6da6359e6963113a1170de795e4b725b84d1e0b4cfd9ec58ce9</td>\n",
       "\t\t<td>19</td>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>4c26d9074c27d89ede59270c0ac14b71e071b15239519f75474b2f3ba63481f5</td>\n",
       "\t\t<td>19</td>\n",
       "\t</tr>\n",
       "</table>"
      ],
      "text/plain": [
       "------------------------------------------------------------------------\n",
       " name                                                             | age \n",
       "------------------------------------------------------------------+-----\n",
       " 2bd806c97f0e00af1a1fc3328fa763a9269723c8db8fac4f93af71db186d6e90 |  18 \n",
       " 81b637d8fcd2c6da6359e6963113a1170de795e4b725b84d1e0b4cfd9ec58ce9 |  19 \n",
       " 4c26d9074c27d89ede59270c0ac14b71e071b15239519f75474b2f3ba63481f5 |  19 \n",
       "------------------------------------------------------------------------\n",
       "(3 rows)"
      ]
     },
     "execution_count": 25,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from dataclasses import dataclass, asdict\n",
    "\n",
    "\n",
    "@dataclass\n",
    "class Student:\n",
    "    name: str\n",
    "    age: int\n",
    "\n",
    "\n",
    "def hide_name(name: str, age: int) -> Student:\n",
    "    return Student(name=sha256(name.encode(\"utf-8\")).hexdigest(), age=age)\n",
    "\n",
    "\n",
    "student.apply(lambda t: gp.create_function(hide_name)(t[\"name\"], t[\"age\"]), expand=True)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can directly apply the same Python function without any modification to the `DataFrame` in pandas:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2bd806c97f0e00af1a1fc3328fa763a9269723c8db8fac...</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>81b637d8fcd2c6da6359e6963113a1170de795e4b725b8...</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4c26d9074c27d89ede59270c0ac14b71e071b15239519f...</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                name  age\n",
       "0  2bd806c97f0e00af1a1fc3328fa763a9269723c8db8fac...   18\n",
       "1  81b637d8fcd2c6da6359e6963113a1170de795e4b725b8...   19\n",
       "2  4c26d9074c27d89ede59270c0ac14b71e071b15239519f...   19"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd_df.apply(lambda df: asdict(hide_name(df[\"name\"], df[\"age\"])), axis=1, result_type=\"expand\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "But there are still some important **differences** between the two cases:\n",
    "\n",
    "- In pandas, what we apply to a `DataFrame` is a Python function, while in GreenplumPython, a Python function must be converted to a database function before being applied to a `DataFrame`.\n",
    "- pandas supports applying a function along different axes, while GreenplumPython only supports applying a function to each row due to the limitation of the database system behind.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Grouping\n",
    "\n",
    "Like ordering, Grouping data based on distinct set of values of columns can also facilitate analytical tasks.\n",
    "\n",
    "Data grouping is often associated with aggregate functions to obtain data summaries. \n",
    "\n",
    "For example, suppose we want to the number of students of different age. In pandas, we can do:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "age\n",
       "18    1\n",
       "19    2\n",
       "dtype: int64"
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "\n",
    "pd_df.groupby(\"age\").apply(lambda df: np.count_nonzero(df[\"name\"]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In GreenplumPython, what we need to do is:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "\t<tr>\n",
       "\t\t<th>count</th>\n",
       "\t\t<th>age</th>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>2</td>\n",
       "\t\t<td>19</td>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>1</td>\n",
       "\t\t<td>18</td>\n",
       "\t</tr>\n",
       "</table>"
      ],
      "text/plain": [
       "-------------\n",
       " count | age \n",
       "-------+-----\n",
       "     2 |  19 \n",
       "     1 |  18 \n",
       "-------------\n",
       "(2 rows)"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "count = gp.aggregate_function(\"count\")\n",
    "\n",
    "student.group_by(\"age\").apply(lambda t: count(t[\"name\"]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Data Deduplication\n",
    "\n",
    "Data deduplication is to return a new data structure containing only the distinct set of values in the selected columns.\n",
    "\n",
    "This operation is well supported in GreenplumPython, also in a very similar way to pandas.\n",
    "\n",
    "For example, suppose we want to draw a representative sample containing students for each distinct age,\n",
    "\n",
    "With pandas, we can do:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>alice</td>\n",
       "      <td>18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bob</td>\n",
       "      <td>19</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name  age\n",
       "0  alice   18\n",
       "1    bob   19"
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd_df.drop_duplicates(\"age\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With GreenplumPython, what we need to do is:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "\t<tr>\n",
       "\t\t<th>name</th>\n",
       "\t\t<th>age</th>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>alice</td>\n",
       "\t\t<td>18</td>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>carol</td>\n",
       "\t\t<td>19</td>\n",
       "\t</tr>\n",
       "</table>"
      ],
      "text/plain": [
       "-------------\n",
       " name  | age \n",
       "-------+-----\n",
       " alice |  18 \n",
       " carol |  19 \n",
       "-------------\n",
       "(2 rows)"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "student.distinct_on(\"age\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Moreover, GreenplumPython also supports aggregation on only the distinct values.\n",
    "\n",
    "Suppose we want to know the number of different ages of the students, we can do:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "\t<tr>\n",
       "\t\t<th>count</th>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>2</td>\n",
       "\t</tr>\n",
       "</table>"
      ],
      "text/plain": [
       "-------\n",
       " count \n",
       "-------\n",
       "     2 \n",
       "-------\n",
       "(1 row)"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "student.apply(lambda t: count.distinct(t[\"age\"]))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Joins\n",
    "\n",
    "Joins are operations combining two data structures horizontally in a sensible way.\n",
    "\n",
    "This makes it easier and more efficient to query one data structure based on the other.\n",
    "\n",
    "For example, suppose we want to retrieve all pairs of sturents of the same age.\n",
    "\n",
    "In pandas, we can join the `DataFrame` with itself on the \"age\" column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>age</th>\n",
       "      <th>name_2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>alice</td>\n",
       "      <td>18</td>\n",
       "      <td>alice</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>bob</td>\n",
       "      <td>19</td>\n",
       "      <td>bob</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>bob</td>\n",
       "      <td>19</td>\n",
       "      <td>carol</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>carol</td>\n",
       "      <td>19</td>\n",
       "      <td>bob</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>carol</td>\n",
       "      <td>19</td>\n",
       "      <td>carol</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    name  age name_2\n",
       "0  alice   18  alice\n",
       "1    bob   19    bob\n",
       "2    bob   19  carol\n",
       "3  carol   19    bob\n",
       "4  carol   19  carol"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd_df.merge(pd_df, on=\"age\", suffixes=(\"\", \"_2\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Similarily, in GreenplumPython, we can do:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "\t<tr>\n",
       "\t\t<th>name</th>\n",
       "\t\t<th>age</th>\n",
       "\t\t<th>name_2</th>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>bob</td>\n",
       "\t\t<td>19</td>\n",
       "\t\t<td>carol</td>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>bob</td>\n",
       "\t\t<td>19</td>\n",
       "\t\t<td>bob</td>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>carol</td>\n",
       "\t\t<td>19</td>\n",
       "\t\t<td>carol</td>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>carol</td>\n",
       "\t\t<td>19</td>\n",
       "\t\t<td>bob</td>\n",
       "\t</tr>\n",
       "\t<tr>\n",
       "\t\t<td>alice</td>\n",
       "\t\t<td>18</td>\n",
       "\t\t<td>alice</td>\n",
       "\t</tr>\n",
       "</table>"
      ],
      "text/plain": [
       "----------------------\n",
       " name  | age | name_2 \n",
       "-------+-----+--------\n",
       " bob   |  19 | carol  \n",
       " bob   |  19 | bob    \n",
       " carol |  19 | carol  \n",
       " carol |  19 | bob    \n",
       " alice |  18 | alice  \n",
       "----------------------\n",
       "(5 rows)"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "student.join(student, on=\"age\", other_columns={\"name\": \"name_2\"})"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "In terms of associative query, there is an important **difference** between GreenplumPython and pandas.\n",
    "\n",
    "pandas allows querying one `DataFrame` based on another without previously joining them. \n",
    "\n",
    "For example, suppose we have two pandas `DataFrame` of numbers, the operation below is *legal* even though not *sensible*:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>val</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   val\n",
       "0    1\n",
       "1    3\n",
       "2    5\n",
       "3    7\n",
       "4    9"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "num_1 = pd.DataFrame({\"val\": [1, 3, 5, 7, 9]})\n",
    "num_2 = pd.DataFrame({\"val\": [2, 4, 6, 8, 10]})\n",
    "\n",
    "num_1[num_2[\"val\"] % 2 == 0]  # Even numbers?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To avoid such kind of misuse, in GreenplumPython, it is impossible to refer to other `DataFrame`s except for the \"current\" one in an expression except when using the `in_()` expression.\n",
    "\n",
    "This is because GreenplumPython only accepts a `Callable` as argument for expression and will bind it to the current `DataFrame` automatically. "
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  },
  "vscode": {
   "interpreter": {
    "hash": "aee8b7b246df8f9039afb4144a1f6fd8d2ca17a180786b69acc140d282b71a49"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
